﻿using Dapper;
using DapperExtensions;
using DapperExtensions.Mapper;
using DapperExtensions.Sql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection; 

namespace WeChatParking.Repository
{
    public interface IDbContext
    {

    }

    public class DbContext : Database, IDbContext
    {
        private DbContext(IDbConnection conn)
            : base(conn ?? DbHelper.Connection, SqlGeneratorImpl)
        {
        }

        private int? _timeout = null;

        public int TimeOut
        {
            set
            {
                this._timeout = value;
            }
        }

        public static DbContext Create(IDbConnection conn = null)
        {
            return new DbContext(conn);
        }

        private static ISqlGenerator _sqlGeneratorImpl = null;
        private static ISqlGenerator SqlGeneratorImpl
        {
            get
            {
                if (_sqlGeneratorImpl == null)
                {
                    _sqlGeneratorImpl = new SqlGeneratorImpl(new DapperExtensionsConfiguration(typeof(AutoClassMapper<>), WeChatParking.Repository.Mapper.MapperConfig.Init(), new SqlServerDialect()));
                }
                return _sqlGeneratorImpl;
            }
        }

        private string CurrentUser
        {
            get
            {
                return "SYSTEM";
            }
        }

        private void SetSimpleEntityOnInsert<T>(T entity)
        {
            if (entity is Entity.SimpleEntity)
            {
                var x = entity as Entity.SimpleEntity;
                x.create_time = x.update_time = DateTime.Now;
                x.create_user = x.update_user = CurrentUser;
                x.version = 0;
            }
        }

        private void SetSimpleEntityOnUpdate<T>(T entity)
        {
            if (entity is Entity.SimpleEntity)
            {
                var x = entity as Entity.SimpleEntity;
                x.update_time = DateTime.Now;
                x.update_user = CurrentUser;
                x.version++;
            }
        }

        #region override

        public void Insert<T>(IEnumerable<T> entities, IDbTransaction transaction) where T : class
        {
            //foreach (var entity in entities)
            //{
            //    SetSimpleEntityOnInsert(entity);
            //}

            base.Insert<T>(entities, transaction, _timeout);
        }


        public void Insert<T>(IEnumerable<T> entities) where T : class
        {
            //foreach (var entity in entities)
            //{
            //    SetSimpleEntityOnInsert(entity);
            //}
            base.Insert(entities, _timeout);
        }

        public dynamic Insert<T>(T entity, IDbTransaction transaction) where T : class
        {
           // SetSimpleEntityOnInsert(entity);
            return base.Insert(entity, transaction, _timeout);
        }

        public dynamic Insert<T>(T entity) where T : class
        {
            //SetSimpleEntityOnInsert(entity);
            return base.Insert(entity, _timeout);
        }


        public bool Update<T>(T entity, IDbTransaction transaction) where T : class
        {
            //SetSimpleEntityOnUpdate(entity);
            return base.Update(entity, transaction, _timeout);
        }

        public bool Update<T>(T entity) where T : class
        {
            //SetSimpleEntityOnUpdate(entity);
            return base.Update(entity, _timeout);
        }

        public void Update<T>(IEnumerable<T> entities) where T : class
        {
            foreach (var e in entities)
            {
               // SetSimpleEntityOnUpdate(e);
                base.Update(e, _timeout);
            }
        }

        public IEnumerable<T> GetList<T>(object predicate, IList<ISort> sort = null) where T : class
        {
            return base.GetList<T>(predicate, sort, _timeout, false);
        }

        public Entity.PageList<T> GetPageList<T>(object predicate, IList<ISort> sort, Entity.BaseFilter filter) where T : class
        {
            Entity.PageList<T> data = new Entity.PageList<T>();
            data.Total = base.Count<T>(predicate, _timeout);
            data.TotalPage = data.Total / filter.rows + (data.Total % filter.rows == 0 ? 0 : 1);
            data.Page = filter.page;
            if (data.Total == 0)
            {
                data.DataList = new List<T>();
            }
            else if (data.Total <= filter.rows)
            {
                if (filter.page > 1)
                    data.DataList = new List<T>();
                else
                    data.DataList = base.GetList<T>(predicate, sort, _timeout, false).ToList();
            }
            else
            {
                data.DataList = base.GetPage<T>(predicate, sort, filter.page - 1, filter.rows, _timeout, false).ToList();
            }

            return data;
        }

        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <typeparam name="T">实体对象</typeparam>
        /// <param name="predicate"></param>
        /// <param name="sort"></param>
        /// <param name="page">页数。从0开始</param>
        /// <param name="resultsPerPage">每页记录数</param>
        /// <param name="buffered"></param>
        /// <returns></returns>
        public Entity.PageList<T> GetPageList<T>(object predicate, IList<ISort> sort, int page, int resultsPerPage, bool buffered = false) where T : class
        {
            Entity.PageList<T> data = new Entity.PageList<T>();
            data.Total = base.Count<T>(predicate, _timeout);
            data.TotalPage = data.Total / resultsPerPage + (data.Total % resultsPerPage == 0 ? 0 : 1);
            data.Page = page;
            if (data.Total == 0)
            {
                data.DataList = new List<T>();
            }
            else if (data.Total <= resultsPerPage)
            {
                if (page > 0)
                    data.DataList = new List<T>();
                else
                    data.DataList = base.GetList<T>(predicate, sort, _timeout, buffered).ToList();
            }
            else
            {
                data.DataList = base.GetPage<T>(predicate, sort, page, resultsPerPage, _timeout, buffered).ToList();
            }

            return data;
        }

        #endregion


        #region Query

        public int GetCount(string sql, dynamic predicate)
        {
            string countSql = GetCountSql(sql);

            int total = (int)this.Connection.Query(countSql, (object)predicate, null, false, _timeout, CommandType.Text).Single().Total;

            return total;
        }

        public IEnumerable<T> GetListBySql<T>(string sql, dynamic predicate)
        {
            var data = this.Connection.Query<T>(sql, (object)predicate, null, false, _timeout, CommandType.Text);
            return data;
        }

        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <typeparam name="T">实体对象</typeparam>
        /// <param name="sql"></param>
        /// <param name="predicate"></param>
        /// <param name="page">页数。从0开始</param>
        /// <param name="resultsPerPage">每页记录数</param>
        public Entity.PageList<T> GetListBySql<T>(string sql, dynamic predicate, int page, int resultsPerPage) where T : class
        {
            sql = sql.Trim();
            Entity.PageList<T> data = new Entity.PageList<T>();

            data.Total = GetCount(sql, predicate);
            data.TotalPage = data.Total / resultsPerPage + (data.Total % resultsPerPage == 0 ? 0 : 1);
            data.Page = page + 1;
            if (data.Total == 0)
            {
                data.DataList = new List<T>();
            }
            else if (data.Total <= resultsPerPage)
            {
                if (page > 0)
                    data.DataList = new List<T>();
                else
                    data.DataList = this.GetListBySql<T>(sql, (object)predicate).ToList();
            }
            else
            {
                string newSql = GetPagedSql(sql, page, resultsPerPage, predicate);

                data.DataList = this.GetListBySql<T>(newSql, (object)predicate).ToList();
            }

            return data;
        }

        private string GetCountSql(string sql)
        {
            string orderByClause = GetOrderByClause(sql);
            string newSql = "select count(*) as Total from ("
                + sql.Replace(" " + orderByClause, string.Empty) + ")t";
            return newSql;
        }

        private string GetPagedSql(string sql, int page, int resultsPerPage, dynamic predicate)
        {
            int selectIndex = GetSelectEnd(sql);
            string orderByClause = GetOrderByClause(sql);
            if (orderByClause == null)
            {
                orderByClause = "ORDER BY CURRENT_TIMESTAMP";
            }


            string newSql = sql
                .Replace(" " + orderByClause, string.Empty)
                .Insert(selectIndex, string.Format(" ROW_NUMBER() OVER(ORDER BY {0}) AS [{1}], ", orderByClause.Substring(9), "_row_number"));

            string result = string.Format("SELECT TOP({0}) {1} FROM ({2}) [_proj] WHERE [_proj].[{3}] >= @_pageStartRow ORDER BY [_proj].[{3}]",
                resultsPerPage, "*", newSql, "_row_number");

            predicate._pageStartRow = page * resultsPerPage + 1;

            return result;
        }

        protected int GetSelectEnd(string sql)
        {
            if (sql.StartsWith("SELECT DISTINCT", StringComparison.InvariantCultureIgnoreCase))
            {
                return 15;
            }

            if (sql.StartsWith("SELECT", StringComparison.InvariantCultureIgnoreCase))
            {
                return 6;
            }

            throw new ArgumentException("SQL must be a SELECT statement.", "sql");
        }

        protected string GetOrderByClause(string sql)
        {
            int orderByIndex = sql.LastIndexOf(" ORDER BY ", StringComparison.InvariantCultureIgnoreCase);
            if (orderByIndex == -1)
            {
                return null;
            }

            string result = sql.Substring(orderByIndex).Trim();

            int whereIndex = result.IndexOf(" WHERE ", StringComparison.InvariantCultureIgnoreCase);
            if (whereIndex == -1)
            {
                return result;
            }

            return result.Substring(0, whereIndex).Trim();
        }


        #endregion

        #region 存储过程

        public IEnumerable<T> Execute<T>(string sql, DynamicParameters param, CommandType commandType = CommandType.StoredProcedure)
        {
            return this.Connection.Query<T>(sql, param, null, false, null, commandType);
        }

        public void Execute(string sql, DynamicParameters param, CommandType commandType = CommandType.StoredProcedure)
        {
            this.Connection.Execute(sql, param, null, null, commandType);
        }

        #endregion
    }
}
